﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Catering_Operation
{
    public class ViewOperaction
    {
        public DataSet GetViewToDataSet(string strViewName)
        {
            DataSet ds_View = new DataSet();

            if (strViewName.Length > 0)
            {
                string strSql = "Select * from " + strViewName;
                ds_View = Catering_DBOpertion.DbHelperSQL.Query(strSql);
            }
            return ds_View;
        }
        public Int32 GetSingle(string strTableName, string strColumName, string strWhere = null)
        {
            Int32 Number = 0;
            Boolean bl = false;
            string strSql = string.Empty;
            if (strTableName.Length > 0)
            {
                if (strWhere == null)
                {
                    strSql = "select " + strColumName + " from " + strTableName;
                }
                else
                {
                    strSql = "select " + strColumName + " from " + strTableName + " where " + strWhere;
                }

                object obj = Catering_DBOpertion.DbHelperSQL.GetSingle(strSql);
                if (obj != null)
                {
                    bl = Int32.TryParse(obj.ToString(), out Number);
                }
            }
            return Number;

        }
        public string GetSingle_str(string strTableName, string strColumName, string strWhere)
        {
            string Number = string.Empty;

            string strSql = string.Empty;
            if (strTableName.Length > 0)
            {
                if (strWhere == null)
                {
                    strSql = "select " + strColumName + " from " + strTableName;
                }
                else
                {
                    strSql = "select " + strColumName + " from " + strTableName + " where " + strWhere;
                }

                object obj = Catering_DBOpertion.DbHelperSQL.GetSingle(strSql);
                if (obj != null)
                {
                    Number = obj.ToString();
                }
            }
            return Number;

        }
        public Int32 GetStrsql(string strViewName)
        {
            Int32 Number = 0;
            Boolean bl = false;
            if (strViewName.Length > 0)
            {
                string strSql = "select (" +
                "select sum(CONVERT(FLOAT,info.Weight) )from InDbInfo as info where info.IntDate<='" + strViewName + "')-(" +
               "select sum(Convert(float,fr.Numbers))from FromRecords as fr where fr.OutDate<= '" + strViewName + "')";
                object obj = Catering_DBOpertion.DbHelperSQL.GetSingle(strSql);
                if (obj != null)
                {
                    bl = Int32.TryParse(obj.ToString(), out Number);
                }
            }
            return Number;
        }


        public DataSet GetcaigoujihuaToDataSet(string strViewName)
        {
            DataSet ds_View = new DataSet();
            System.Text.StringBuilder sb = new StringBuilder();

            sb.Append("select raid,rigropid, riclassid,dbids,");
            sb.Append("Sum(jihua) as jihua ,raid ,sum(yuji) as yuji ,");
            sb.Append("RAWName  from Caigoujihua ");
            sb.Append("   where  " + strViewName);
            sb.Append("  group by raid,rigropid,riclassid,dbids,RAWName ");
            if (strViewName.Length > 0)
            {
                ds_View = Catering_DBOpertion.DbHelperSQL.Query(sb.ToString());
            }
            return ds_View;
        }

        public DataSet GetKcpd(string whereClause, DateTime datetime, string orderby)
        {
            #region old

            //StringBuilder StrSql = new StringBuilder("select InDb.id,InDb.[RAW],InDb.CMP,InDb.temp1 AS Number,InDb.GroupID,InDb.ClassID,InDb.IntDate,");
            //StrSql.Append("InDb.RAWTID,CONVERT(FLOAT, InDb.temp1) * CONVERT(FLOAT, InDb.temp2) AS Moeny,");
            //StrSql.Append("InDb.temp2 AS pice,Mater.MaterialsInfoName,Mater.MaterialID,");
            //StrSql.Append("Mater.MaterialsInfoID,Mater.MaterialsID,Mater.StorageID ");
            //StrSql.Append(" From InDbInfo AS InDb");
            //StrSql.Append(" left join View_MaterialFamily AS Mater ON InDb.RAWID=Mater.MaterialID ");
            //StrSql.Append(" WHERE (InDb.id IN (SELECT  MAX(id) AS Expr1  FROM dbo.InDbInfo WHERE 1=1 ");
            //if (datetime > DateTime.MinValue) StrSql.Append(" AND IntDate<'" + datetime.AddDays(1).ToShortDateString() + "' ");
            //StrSql.Append(" GROUP BY ClassID,RAWID )) ");
            //if (!string.IsNullOrEmpty(whereClause)) StrSql.Append(whereClause);
            //StrSql.Append(" ORDER BY Mater.MaterialsInfoName,RAW ");
            //return Catering_DBOpertion.DbHelperSQL.Query(StrSql.ToString());

            #endregion

            StringBuilder StrSql = new StringBuilder("select * from (select *,RANK() over(partition by ClassID,RawID,InDBID order by AddDate Desc) AS [Rank]");
            StrSql.Append(" from (select B.*,R.* from Balance AS B ");
            StrSql.Append(" left join View_MaterialFamily AS R ");
            StrSql.Append(" on B.RawID=R.MaterialID WHERE 1=1 ");
            if (!string.IsNullOrEmpty(whereClause)) StrSql.Append(whereClause);
            if (datetime > DateTime.MinValue) StrSql.Append(" AND B.AddDate<'" + datetime.AddDays(1).ToShortDateString() + "' ");
            StrSql.Append(" ) AS A) AS Tab where [Rank]=1 ");
            if (!string.IsNullOrEmpty(orderby)) StrSql.Append(" ORDER BY " + orderby);
            return Catering_DBOpertion.DbHelperSQL.Query(StrSql.ToString());

        }

        public decimal GetKuCunAmontMoneyByMonth(string whereClause)
        {
            string sql = "SELECT SUM(AmountMoney) From FromRecords AS fr LEFT JOIN View_MaterialFamily AS vw ON fr.RAWID=vw.MaterialID WHERE 1=1 " + whereClause;
            Object number = Catering_DBOpertion.DbHelperSQL.GetSingle(sql);
            if (number != null) return Convert.ToDecimal(number); else return 0;
        }


        public DataSet GetViewToDataSet(string strViewName, string strWhere)
        {
            DataSet ds_View = new DataSet();

            if (strViewName.Length > 0 && strWhere.Length > 0)
            {
                string strSql = "Select * from " + strViewName + "  where  " + strWhere;
                ds_View = Catering_DBOpertion.DbHelperSQL.Query(strSql);
            }
            return ds_View;
        }

        public Boolean DataTableInsertToServer(DataTable dt, string strName, DataColumnCollection dtColum)
        {
            return Catering_DBOpertion.DbHelperSQL.UpdateDataTableToServer(dt, strName, dtColum);
        }

        #region 报表
        /// <summary>
        /// 消耗分类汇总数据
        /// </summary>
        /// <returns></returns> 
        public DataSet GetViewSumToDataSet()
        {
            DataSet ds = new DataSet();

            string strSql = "SELECT * FROM View_Xiaohao_TypeSum";
            ds = Catering_DBOpertion.DbHelperSQL.Query(strSql);

            return ds;
        }

        /// <summary>
        /// 累计百分比数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetViewTotalToDataTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("TypeName"));
            dt.Columns.Add(new DataColumn("Numbers"));
            dt.Columns.Add(new DataColumn("CName"));
            dt.Columns.Add(new DataColumn("CMP"));
            dt.Columns.Add(new DataColumn("month"));
            IDataParameter[] parameters = new IDataParameter[] { };

            System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();

            System.Data.SqlClient.SqlDataReader sdr
                = Catering_DBOpertion.DbHelperSQL.RunProcedure("Proc_TotalPercent", parameters);
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["TypeName"] = sdr["TypeName"];
                dr["Numbers"] = sdr["Numbers"];
                dr["CName"] = sdr["CName"];
                dr["CMP"] = sdr["CMP"];
                dr["month"] = sdr["month"];
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 人均消耗分类汇总
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonTypeSumByProc_PersonType()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add(new DataColumn("Numbers"));
            dt.Columns.Add(new DataColumn("Eat"));
            dt.Columns.Add(new DataColumn("TypeName"));
            dt.Columns.Add(new DataColumn("Name"));
            dt.Columns.Add(new DataColumn("Month"));
            dt.Columns.Add(new DataColumn("OutDate"));
            dt.Columns.Add(new DataColumn("CMP"));

            System.Data.SqlClient.SqlDataReader sdr
                = Catering_DBOpertion.DbHelperSQL.RunProcedure("Proc_Persontype", new IDataParameter[] { });
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();

                dr["Numbers"] = sdr["Numbers"];
                dr["Eat"] = sdr["Eat"];
                dr["TypeName"] = sdr["TypeName"];
                dr["Name"] = sdr["Name"];
                dr["Month"] = sdr["Month"];
                dr["OutDate"] = sdr["OutDate"];
                dr["CMP"] = sdr["CMP"];
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 月累计百分比
        /// </summary>
        /// <returns></returns>
        public DataTable GetMonthTotalByProc_TotalMonth(string month)
        {
            DataTable dt = new DataTable();

            dt.Columns.Add(new DataColumn("AreaName"));
            dt.Columns.Add(new DataColumn("CName"));
            dt.Columns.Add(new DataColumn("TypeName"));
            dt.Columns.Add(new DataColumn("CMP"));
            dt.Columns.Add(new DataColumn("OutDate"));
            dt.Columns.Add(new DataColumn("Month"));
            dt.Columns.Add(new DataColumn("Numbers"));

            System.Data.SqlClient.SqlDataReader sdr
                = Catering_DBOpertion.DbHelperSQL.RunProcedure(
                "Proc_TotalMonth",
                new SqlParameter[] { new SqlParameter("@Month", month) }
                );
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["AreaName"] = sdr["AreaName"];
                dr["CName"] = sdr["CName"];
                dr["TypeName"] = sdr["TypeName"];
                dr["CMP"] = sdr["CMP"];
                dr["OutDate"] = sdr["OutDate"];
                dr["Month"] = sdr["Month"];
                dr["Numbers"] = sdr["Numbers"];
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 人均食用油统计
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonEdibleByProc_PersonEdibleStatistical(string Date)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("AreaName"));
            dt.Columns.Add(new DataColumn("CName"));
            dt.Columns.Add(new DataColumn("Eat"));
            dt.Columns.Add(new DataColumn("CMP"));
            dt.Columns.Add(new DataColumn("OutDate"));
            dt.Columns.Add(new DataColumn("Month"));
            dt.Columns.Add(new DataColumn("Numbers"));
            dt.Columns.Add(new DataColumn("标准用油"));
            dt.Columns.Add(new DataColumn("超量"));
            dt.Columns.Add(new DataColumn("人均值"));
            System.Data.SqlClient.SqlDataReader sdr
                = Catering_DBOpertion.DbHelperSQL.RunProcedure(
                "Proc_PersonEdible_Statistical",
                new SqlParameter[] { new SqlParameter("@Date", Date) }
                );
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["AreaName"] = sdr["AreaName"];
                dr["CName"] = sdr["CName"];
                dr["Eat"] = sdr["Eat"];
                dr["CMP"] = sdr["CMP"];
                dr["OutDate"] = sdr["OutDate"];
                dr["Month"] = sdr["Month"];
                dr["Numbers"] = sdr["Numbers"];
                dr["标准用油"] = sdr["标准用油"];
                dr["超量"] = sdr["超量"];
                dr["人均值"] = sdr["人均值"];
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 食用油统计——明细
        /// </summary>
        /// <returns></returns>
        public DataSet GetView_EdibleStatistics_ListToDataSet()
        {
            DataSet ds = new DataSet();
            string strSql = "select * from View_EdibleStatistics_List";
            ds = Catering_DBOpertion.DbHelperSQL.Query(strSql);
            return ds;
        }

        /// <summary>
        /// 食用油统计——全年
        /// </summary>
        /// <returns></returns> 
        public DataTable GetEdibleStatistics_YearByProc_YearEdible()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add(new DataColumn("Name"));
            dt.Columns.Add(new DataColumn("Value"));
            System.Data.SqlClient.SqlDataReader sdr = Catering_DBOpertion.DbHelperSQL.RunProcedure("Proc_YearEdible", new SqlParameter[] { });
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["Name"] = sdr["Name"];
                dr["Value"] = sdr["Value"];
                dt.Rows.Add(dr);
            }
            return dt;
        }
        #endregion

        /// <summary>
        /// 食用油月对比
        /// </summary>
        /// <returns></returns>
        public DataTable GetEdibleStatistics_MonthByProc_EdibleMonth(string Month, string CName)
        {
            DataTable dt = new DataTable();

            dt.Columns.Add(new DataColumn("需采购量"));
            dt.Columns.Add(new DataColumn("消耗量"));
            dt.Columns.Add(new DataColumn("标准值"));
            System.Data.SqlClient.SqlDataReader sdr
                = Catering_DBOpertion.DbHelperSQL.RunProcedure(
                "proc_EdibleMonth",
                new SqlParameter[] { new SqlParameter("@Month", Month), new SqlParameter("@CName", CName) }
                );
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["需采购量"] = sdr["需采购量"];
                dr["消耗量"] = sdr["消耗量"];
                dr["标准值"] = sdr["标准值"];
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 食用油消耗分类汇总
        /// </summary>
        /// <param name="areaName"></param>
        /// <param name="cName"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoSumByprocEdibleConSum(string areaName, string cName, string month)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("Month"));
            dt.Columns.Add(new DataColumn("要料量"));
            dt.Columns.Add(new DataColumn("消耗量"));
            dt.Columns.Add(new DataColumn("标准值"));
            dt.Columns.Add(new DataColumn("超量"));
            dt.Columns.Add(new DataColumn("就餐人数"));
            dt.Columns.Add(new DataColumn("人均值"));
            System.Data.SqlClient.SqlDataReader sdr = Catering_DBOpertion.DbHelperSQL.RunProcedure(
    "[Proc_XiaohaoFenleiSum]", new SqlParameter[] { 
        new SqlParameter("@Zuoyequ", areaName), 
        new SqlParameter("@Banzu", cName) ,
        new SqlParameter ("@Month",month )    });
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["Month"] = sdr["Month"];
                dr["要料量"] = sdr["要料量"];
                dr["消耗量"] = sdr["消耗量"];
                dr["标准值"] = sdr["标准值"];
                dr["超量"] = sdr["超量"];
                dr["就餐人数"] = sdr["就餐人数"];
                dr["人均值"] = sdr["人均值"];
                dt.Rows.Add(dr);
            }
            return dt;
        }


        /// <summary>
        /// 食用油月对比--视图查询
        /// </summary>
        /// <returns></returns>
        public DataSet GetEdibleDuibiByView()
        {
            DataSet ds = new DataSet();
            string strSql = " select * from View_PublicClassType";
            ds = Catering_DBOpertion.DbHelperSQL.Query(strSql);
            return ds;
        }

        /// <summary>
        /// 食用油消耗分类汇总
        /// </summary>
        /// <param name="areaName"></param>
        /// <param name="cName"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoSumByprocEdibleConSum(int isAll, string areaName, string cName, DateTime stare, DateTime end)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("Month"));
            dt.Columns.Add(new DataColumn("要料量"));
            dt.Columns.Add(new DataColumn("消耗量"));
            dt.Columns.Add(new DataColumn("标准值"));
            dt.Columns.Add(new DataColumn("超量"));
            dt.Columns.Add(new DataColumn("就餐人数"));
            dt.Columns.Add(new DataColumn("人均值"));
            System.Data.SqlClient.SqlDataReader sdr = Catering_DBOpertion.DbHelperSQL.RunProcedure(
    "[Proc_XiaohaoFenleiSum]", new SqlParameter[] { 
        new SqlParameter("@Zuoyequ", areaName), 
        new SqlParameter("@Banzu", cName) ,
        new SqlParameter ("@Stare",stare )    });
            while (sdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["Month"] = sdr["Month"];
                dr["要料量"] = sdr["要料量"];
                dr["消耗量"] = sdr["消耗量"];
                dr["标准值"] = sdr["标准值"];
                dr["超量"] = sdr["超量"];
                dr["就餐人数"] = sdr["就餐人数"];
                dr["人均值"] = sdr["人均值"];
                dt.Rows.Add(dr);
            }
            return dt;
        }
    }
}
